﻿<?php
require 'config_bd.php';
require 'cle_encrypt.php';

$uname = $_REQUEST["name"];
$upass = $_REQUEST["thePass"];
$pdate = $_REQUEST["theDate"];

$sql =        "select name as year_total,round(sum(s.hours),2) as year_hours, round(sum(s.hours)/(get_parm_base_hebd_hours(get_user_id( :uname, AES_ENCRYPT(:upass ,:cle_encrypt) ),s.cdate)/5),2) as year_days ";
$sql = $sql . "from summary_base_vw s ";
$sql = $sql . "where :pdate between s.first_day_year and s.last_day_year and ";
$sql = $sql . "       s.user_id = get_user_id( :uname, AES_ENCRYPT(:upass ,:cle_encrypt)  ) ";
$sql = $sql . "group by name "; 
$sql = $sql . " union all ";
$sql = $sql . "select 'Total' as year_total,round(get_total_hours_year(get_user_id( :uname, AES_ENCRYPT(:upass ,:cle_encrypt) ), :pdate),2) as year_hours ,round(get_total_days_year(get_user_id( :uname, AES_ENCRYPT(:upass ,:cle_encrypt) ), :pdate),2) as year_days ";
$sql = $sql . " union all ";
$sql = $sql . " select 'Requis',round(get_required_hours_year (get_user_id( :uname, AES_ENCRYPT(:upass ,:cle_encrypt) ), :pdate),2),round(get_count_business_day_year(:pdate),2) ";
$sql = $sql . "  union all ";
$sql = $sql . " select 'Diff',round(get_total_hours_year(get_user_id( :uname, AES_ENCRYPT(:upass ,:cle_encrypt) ), :pdate) - get_required_hours_year (get_user_id( :uname, AES_ENCRYPT(:upass ,:cle_encrypt) ), :pdate),2) , round(get_total_days_year(get_user_id( :uname, AES_ENCRYPT(:upass ,:cle_encrypt) ), :pdate) - get_count_business_day_year(:pdate),2) ";
$sql = $sql . "  union all ";
$sql = $sql . " select 'Vacances', ";
$sql = $sql . "        round(((get_total_hours_year(get_user_id( :uname, AES_ENCRYPT(:upass ,:cle_encrypt) ), :pdate) - get_required_hours_year (get_user_id( :uname, AES_ENCRYPT(:upass ,:cle_encrypt) ), :pdate)) + (get_parm_days_vacation (get_user_id( :uname, AES_ENCRYPT(:upass ,:cle_encrypt) ),:pdate) * (get_parm_base_hebd_hours (get_user_id( :uname, AES_ENCRYPT(:upass ,:cle_encrypt) ),:pdate)/5) ) ),2), "; 
$sql = $sql . "        round(((get_total_days_year(get_user_id( :uname, AES_ENCRYPT(:upass ,:cle_encrypt) ), :pdate) - get_count_business_day_year(:pdate)) + get_parm_days_vacation (get_user_id( :uname, AES_ENCRYPT(:upass ,:cle_encrypt) ),:pdate)),2) ";
$sql = $sql . "  union all ";
$sql = $sql . "select 'Cumul', round(sum(((get_total_hours_year(get_user_id( :uname, AES_ENCRYPT(:upass ,:cle_encrypt) ), cast(date_format(concat( at.annee , '-01-01'),'%Y-01-01') as date)) - get_required_hours_year (get_user_id( :uname, AES_ENCRYPT(:upass ,:cle_encrypt) ), cast(date_format(concat( at.annee , '-01-01'),'%Y-01-01') as date))) + (get_parm_days_vacation (get_user_id( :uname, AES_ENCRYPT(:upass ,:cle_encrypt) ),cast(date_format(concat( at.annee , '-01-01'),'%Y-01-01') as date)) * (get_parm_base_hebd_hours (get_user_id( :uname, AES_ENCRYPT(:upass ,:cle_encrypt) ),cast(date_format(concat( at.annee , '-01-01'),'%Y-01-01') as date))/5) ) )),2) as hour, "; 
$sql = $sql . "       round(sum(((get_total_days_year(get_user_id( :uname, AES_ENCRYPT(:upass ,:cle_encrypt) ), cast(date_format(concat( at.annee , '-01-01'),'%Y-01-01') as date)) - get_count_business_day_year(cast(date_format(concat( at.annee , '-01-01'),'%Y-01-01') as date))) + get_parm_days_vacation (get_user_id( :uname, AES_ENCRYPT(:upass ,:cle_encrypt) ),cast(date_format(concat( at.annee , '-01-01'),'%Y-01-01') as date)))),2) as day ";
$sql = $sql . " from (select distinct cast( year(cdate) as char) as annee from t002_fdt where year(cdate) <= year(:pdate)) at ";
 
	   
try {
    $dbh = new PDO("mysql:host=$hostname;dbname=$dbname", $username, $password,array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
	$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
	$stmt = $dbh->prepare($sql);  
	$stmt->bindParam("uname", $uname);
	$stmt->bindParam("upass", $upass);
	$stmt->bindParam("cle_encrypt", $cle_encrypt);
	$stmt->bindParam("pdate", $pdate);
	$stmt->execute();
	$alldata = $stmt->fetchAll(PDO::FETCH_OBJ);
	$dbh = null;
	echo '{"items":'. json_encode($alldata) .'}'; 
} catch(PDOException $e) {
	echo '{"error":{"text":'. $e->getMessage() .'}}'; 
}

?>